import xlrd, xlwt
from common.big_screen_interface import BigScreenInterface
import json
import time
import erniebot         # python 3.9+

class WriteExcel():
    def __init__(self,sheet_name):
        # 创建一个workbook 设置编码  和worksheet
        self.wb = xlwt.Workbook(encoding='utf-8')
        self.ws = self.wb.add_sheet(sheet_name)

    def write_excel_head(self,head_col0,head_col1,head_col2,head_col3,head_col4,head_col5,head_col6,head_col7,head_col8,
                         head_col9,head_col10,head_col11,head_col12,head_col13,head_col14,head_col15):
        self.ws.write(0, 0, head_col0)
        self.ws.write(0, 1, head_col1)
        self.ws.write(0, 2, head_col2)
        self.ws.write(0, 3, head_col3)
        self.ws.write(0, 4, head_col4)
        self.ws.write(0, 5, head_col5)
        self.ws.write(0, 6, head_col6)
        self.ws.write(0, 7, head_col7)
        self.ws.write(0, 8, head_col8)
        self.ws.write(0, 9, head_col9)
        self.ws.write(0, 10, head_col10)
        self.ws.write(0, 11, head_col11)
        self.ws.write(0, 12, head_col12)
        self.ws.write(0, 13, head_col13)
        self.ws.write(0, 14, head_col14)
        self.ws.write(0, 15, head_col15)

    def write_excel_body(self, row, col, value):
        # 数据写入excel,参数对应 行, 列, 值
        self.ws.write(row, col, value)  # 不带样式的写入

    def save_excel(self,excel_name):
        current_time = time.strftime("%Y-%m-%d_%H_%M_%S", time.localtime(time.time()))
        self.wb.save('/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/result/data_{}_{}.xls'.format(excel_name,current_time))



class ReadExcel(object):
    def __init__(self, data_path, sheetname, wenxin_step1_url,wenxin_step1_static,wenxin_step1_strong):
        #定义一个属性接收文件路径
        self.data_path = data_path
        # 定义一个属性接收工作表名称
        self.sheetname = sheetname
        # 使用xlrd模块打开excel表读取数据
        self.data = xlrd.open_workbook(self.data_path)
        # 根据工作表的名称获取工作表中的内容
        self.table = self.data.sheet_by_name(self.sheetname)
        # 获取工作表的有效行数
        self.rowNum = self.table.nrows
        self.wenxin_step1_url = wenxin_step1_url
        self.wenxin_step1_static = wenxin_step1_static
        self.wenxin_step1_strong = wenxin_step1_strong

    def readExcel(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            except_type = self.table.cell_value(i, 1)
            except_chat = self.table.cell_value(i, 2)

            content = self.table.cell_value(i, 3)
            contents = content.split('\n')
            # print(contents)
            for content in contents:
                # 调用文心接口,拿到返回的type和对应的method
                obj = BigScreenInterface(self.wenxin_step1_url)
                start_time = time.strftime("%Y-%m-%d %H:%M:%S",time.localtime(time.time()))
                print(start_time)
                res, r_elapsed = obj.wenxin_step1(content)
                res_dic = json.loads(res)
                # 将content，返回type写入excel
                if self.sheetname == "闲聊类指令":
                    data.append({"content":content, "res":res_dic,"r_elapsed": r_elapsed,"except_type":except_type,'except_chat': except_chat})
        return data

    @staticmethod
    def ernie(content):
        # List supported models
        models = erniebot.Model.list()

        # print(models)
        # ernie-bot             文心一言旗舰版
        # ernie-bot-turbo       文心一言轻量版
        # ernie-bot-4           基于文心大模型4.0版本的文心一言
        # ernie-text-embedding  文心百中语义模型
        # ernie-vilg-v2         文心一格模型

        # Set authentication params
        erniebot.api_type = 'qianfan'
        erniebot.access_token = '24.7f821657841899753575a1ff04fe39f6.2592000.1700829961.282335-38388356'

        # Create a chat completion
        try:
            response = erniebot.ChatCompletion.create(model='ernie-bot',
                                                      messages=[{'role': 'user', 'content': content}])
            res_text = response.get_result()
        except Exception as e:
            res_text = str(e)
        # print(type(response.get_result()))
        return res_text

    def readWenXinPublic(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            content = self.table.cell_value(i, 3)
            expAns = self.table.cell_value(i, 4)
            contents = content.split('\n')
            # print(contents)
            for content in contents:
                ans = self.ernie(content)
                data.append({"content":content, "ans":ans, "expAns":expAns})
        return data

    def readWenXinStong(self):
        data = []
        for i in range(1, self.rowNum):
            # 获取单元格数据
            content = self.table.cell_value(i, 3)
            expAns = self.table.cell_value(i, 4)

            obj = BigScreenInterface(self.wenxin_step1_strong)
            res, r_elapsed = obj.wenxin_chat_strong(content)
            res_dic = json.loads(res)
            try:
                ans = res_dic["result"]
            except Exception as e:
                ans = str(e)
            data.append({"content": content, 'ans': ans,"expAns":expAns, "res": res_dic, "r_elapsed": r_elapsed,})
        return data

def generate_excel_body(data, obj,):
    # data值 [{"content":"放大地图"，"res":{……},"except_type":except_type,"except_method":except_method}, {},{}]
    for i in range(len(data)):
        # 1：第一列
        try:
            cmd = data[i]["content"]
        except Exception as e:
            cmd = "缺少指令"
        # 2：第二列
        try:
            cmdType = data[i]["res"]["data"]["type"]
        except Exception as e:
            cmdType = "无data.type字段"
        # 3:第三列
        if sheetname == "闲聊类指令":
            try:
                if data[i]["res"]["data"]["chat"]:
                    chatVal = '不为空'
                else:
                    chatVal = '为空'
            except Exception as e:
                chatVal = '返回值中无chat字段'
        # 第四列
        expType = data[i]["except_type"]
        # 第五列
        expChatVal = data[i]["except_chat"]
        # 第六列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m["type"] == "请求意图参数":
                    reqChatTime = m["time"]
                    timestamp5 = int(m["timestamp"])
        except Exception as e:
            reqChatTime ="未获取到请求聊天参数时间"
            timestamp5 = "时间戳错误"
        # 第7列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m["type"] == "意图识别返回":
                    respChatTime = m["time"]
        except Exception as e:
            respChatTime ="未获取到专业闲聊返回时间"
        # 第8列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m["type"] == "意图识别返回":
                    respCtrlScreenTime = m["time"]
                    timestamp7 = int(m["timestamp"])
        except Exception as e:
            respCtrlScreenTime ="未获取到意图识别返回时间"
            timestamp7 = '时间戳错误'
        # 第9列
        try:
            for m in data[i]["res"]["data"]["trace"]["chatRes"]:
                if m["type"] == "开始响应":
                    respEndStartTime = m["time"]
                    timestamp8 = int(m["timestamp"])
        except Exception as e:
            respEndStartTime ="未获取到结束请求，开始响应内容时间"
            timestamp8 = '时间戳错误'
        # 第10列
        try:
            totalCost = data[i]["res"]["data"]["trace"]["spendTime"]
        except Exception as e:
            totalCost ="未获取到接口总耗时"
        # 第11列
        try:
            intentCost = (timestamp7 - timestamp5)/1000
        except Exception as e:
            intentCost = '未正常返回时间戳'
        # 第12列
        try:
            ansCost = (timestamp8 - timestamp5)/1000
        except Exception as e:
            ansCost = '未正常返回时间戳'


        # 第13列：结论判断
        if sheetname == "闲聊类指令":
            if cmdType == expType and chatVal == expChatVal:
                obj.write_excel_body(i + 1, 12, "PASS")
            else:
                obj.write_excel_body(i + 1, 12, "FAIL")

        # 第14列：个人计算的总耗时
        try:
            myCost = json.dumps(data[i]["r_elapsed"])
        except Exception as e:
            myCost = '未获取到接口总耗时时间'

        # 第15列：闲聊内容
        try:
            contentVal = str(data[i]["res"]["data"]["chat"])
        except Exception as e:
            contentVal = '获取数据失败'

        # 第16列：备注
        if sheetname != "数据分析类指令":
            try:
                comment = str(data[i]["res"]["data"])
            except Exception as e:
                comment = str(data[i]["res"])


        obj.write_excel_body(i + 1, 0, cmd)                        # 第i+1行，第1列
        obj.write_excel_body(i + 1, 1, cmdType)                        # 第i+1行，第2列
        obj.write_excel_body(i + 1, 2, chatVal)                        # 第i+1行，第3列
        obj.write_excel_body(i + 1, 3, expType)                    # 第i+1行，第4列
        obj.write_excel_body(i + 1, 4, expChatVal)                    # 第i+1行，第5列
        obj.write_excel_body(i + 1, 5, reqChatTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 6, respChatTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 7, respCtrlScreenTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 8, respEndStartTime)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 9, totalCost)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 10, intentCost)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 11, ansCost)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 13, myCost)                    # 第i+1行，第7列
        obj.write_excel_body(i + 1, 14, contentVal)                 # 第i+1行，第7列
        obj.write_excel_body(i + 1, 15, comment)                    # 第i+1行，第7列
    obj.save_excel(sheetname)

def generate_wenxin_public(data, obj):
    obj.write_excel_body(0, 0, "cmd")
    obj.write_excel_body(0, 1, "ans")
    obj.write_excel_body(0, 2, "expAns")
    for i in range(len(data)):
        obj.write_excel_body(i + 1, 0, data[i]['content'])
        obj.write_excel_body(i + 1, 1, data[i]['ans'])
        obj.write_excel_body(i + 1, 2, data[i]['expAns'])
    obj.save_excel('闲聊类指令公版')

def generate_wenxin_strong(data, obj):
    obj.write_excel_body(0, 0, "cmd")
    obj.write_excel_body(0, 1, "ans")
    obj.write_excel_body(0, 2, "expAns")
    for i in range(len(data)):
        obj.write_excel_body(i + 1, 0, data[i]['content'])
        obj.write_excel_body(i + 1, 1, data[i]['ans'])
        obj.write_excel_body(i + 1, 2, data[i]['expAns'])
    obj.save_excel('闲聊类指令增强版')

def main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static,wenxinType,wenxin_step1_strong):
    read_obj = ReadExcel(data_path,sheetname,wenxin_step1_url,wenxin_step1_static,wenxin_step1_strong)
    write_obj = WriteExcel(sheetname)

    if wenxinType == "线上版文心":
        data = read_obj.readExcel()
        write_obj.write_excel_head("指令类型",
                                   "返回的type类型", "返回的chat值",
                                   "预期的type类型", "预期的chat值",
                                   "请求聊天内容时间", '专业闲聊返回时间', '判断控屏返回时间', '请求结束，开始响应时间', '总耗时', '意图识别耗时', '出答案耗时',
                                   "结论", "个人计算的总耗时", "聊天内容", "备注")
        generate_excel_body(data, write_obj)
    elif wenxinType == "公用版文心":
        data = read_obj.readWenXinPublic()
        generate_wenxin_public(data, write_obj)
    elif wenxinType == "增强版文心":
        data = read_obj.readWenXinStong()
        generate_wenxin_strong(data, write_obj)
    else:
        print("请输入正确的文心版本")


if __name__ == "__main__":

    data_path = "/Users/v_baiguanghui/Downloads/云控相关项目/101.测试脚本/bigScreenDigitalPeople/resource/大屏数字人指令集-test.xlsx"

    # wenxinType = "线上版文心"   # 线上版本为增强版
    # wenxinType = "公用版文心"
    wenxinType = "增强版文心"   # 增强版为线上版
    # wenxinType = "开源版文心"
    sheetname_list = ["闲聊类指令"]
    wenxin_step1_url = "https://219.135.214.196:9001/v2x/platform/wenxin/v2/chat"
    wenxin_step1_static = "https://219.135.214.196:9001/v2x/platform/wenxin/chat/statistic"
    wenxin_step1_strong = "https://aip.baidubce.com/rpc/2.0/ai_custom/v1/wenxinworkshop/plugin/wanglian/?access_token=24.7f821657841899753575a1ff04fe39f6.2592000.1700829961.282335-38388356"


    for sheetname in sheetname_list:
        main(data_path,sheetname,wenxin_step1_url,wenxin_step1_static,wenxinType,wenxin_step1_strong)

    # ernie()






